﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using VCN.App.DataTransferObjects;

namespace VCN.App.DataAccessLayer
{
    public class NewsDAO : BasicDAO<News>
    {
        public DataTable SellectNewsTop()
        {
            DataTable v_datasource = new DataTable();
            System.Configuration.AppSettingsReader settingsReader = new AppSettingsReader();
            string driver = (string)settingsReader.GetValue("ConnectionString", typeof(string));
            SqlConnection v_conn = new SqlConnection(driver);
            try
            {
                v_conn.Open();
            }
            catch (Exception exp)
            {
                HttpContext.Current.Response.Write(exp.ToString());
            }
            SqlCommand sqlcom = new SqlCommand("sp_tblNews_SelectTop10", v_conn);
            sqlcom.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter v_sqladapter = new SqlDataAdapter(sqlcom);
            v_sqladapter.Fill(v_datasource);
            try
            {
                sqlcom.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
            }
            return v_datasource;
        }

        public List<News> convert(DataTable ip_dt)
        {
            if (ip_dt == null)
            {
                return null;
            }
            else
            {
                List<News> op_list_news = new List<News>();
                for (int i = 0; i < ip_dt.Rows.Count; i++)
                {
                    DataRow v_dr = ip_dt.Rows[i];
                    News v_news = new News(int.Parse(v_dr["ID"].ToString()), v_dr["TITLE"].ToString(),
                        v_dr["SHORT_CONTENT"].ToString(), v_dr["CONTENTS"].ToString(),
                        v_dr["NEWS_IMAGE"].ToString(), (DateTime)v_dr["CREATED_DATE"],
                        (v_dr["TYPE"].ToString() == "") ? 0 : int.Parse(v_dr["TYPE"].ToString()),
                        (v_dr["AUTHOR_ID"].ToString() == "") ? 0 : int.Parse(v_dr["AUTHOR_ID"].ToString()),
                        v_dr["REFERENCE"].ToString(),
                        (v_dr["VIEW_COUNT"].ToString() == "") ? 0 : int.Parse(v_dr["VIEW_COUNT"].ToString()));                    
                    op_list_news.Add(v_news);
                }
                return op_list_news;
            }
        }
        protected override DataTable exeSpSelect(string ip_storedProcedure)
        {
            return base.exeSpSelect(ip_storedProcedure);//sp_tblCategories_SelectAll
        }
        public DataTable selectAll2Dt()
        {
            return this.exeSpSelect("sp_tblNews_SelectAll");
        }
        protected DataTable exeSpSelectById(string ip_storeProcedure, int ip_id)
        {
            return base.exeSpSelectById(ip_storeProcedure, ip_id);
        }
        public News SelectById(int ip_id)
        {
            DataTable v_dt = this.exeSpSelectById("sp_tblNews_SelectRow", ip_id);
            News v_News = new News();
            v_News.Id = ip_id;
            v_News.Title = v_dt.Rows[0]["TITLE"].ToString();
            v_News.ShortContent = v_dt.Rows[0]["SHORT_CONTENT"].ToString();
            v_News.Contents = v_dt.Rows[0]["CONTENTS"].ToString();
            v_News.NewsImage = v_dt.Rows[0]["NEWS_IMAGE"].ToString();
            v_News.CreatedDate = DateTime.Parse(v_dt.Rows[0]["CREATED_DATE"].ToString());
            v_News.AuthorId = int.Parse(v_dt.Rows[0]["AUTHOR_ID"].ToString());
            v_News.Reference = v_dt.Rows[0]["REFERENCE"].ToString();
            v_News.ViewCount = int.Parse(v_dt.Rows[0]["VIEW_COUNT"].ToString());
            v_News.Type = int.Parse(v_dt.Rows[0]["TYPE"].ToString());
            return v_News;
        }
        public DataTable searchNews(string ip_TieuDe)
        {
            SqlConnection v_conn = BasicDAO<News>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblNews_Search", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = ip_TieuDe;
            DataTable v_dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter(v_command);
            try
            {

                adapter.Fill(v_dt);
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
            }
            return v_dt;
        }

        public override bool insert(News ip_News)
        {
            SqlConnection v_conn = BasicDAO<News>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblNews_Insert", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = ip_News.Title;
            v_command.Parameters.Add("@ShortContent", SqlDbType.NText).Value = ip_News.ShortContent;
            v_command.Parameters.Add("@Contents", SqlDbType.NText).Value = ip_News.Contents;
            v_command.Parameters.Add("@Image", SqlDbType.VarChar).Value = ip_News.NewsImage;
            v_command.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = ip_News.CreatedDate;
            v_command.Parameters.Add("@AuthorId", SqlDbType.Int).Value = ip_News.AuthorId;
            v_command.Parameters.Add("@Reference", SqlDbType.NVarChar).Value = ip_News.Reference;
            v_command.Parameters.Add("@ViewCount", SqlDbType.Int).Value = ip_News.ViewCount;
            v_command.Parameters.Add("@Type", SqlDbType.Int).Value = ip_News.Type;
            try
            {
                int v_rows = v_command.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
        protected DataTable exeSpSelect()
        {
            return base.exeSpSelect("sp_tblNews_select");
        }
        public override bool update(News ip_News)
        {
            SqlConnection v_conn = BasicDAO<News>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblNews_Update", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Id", SqlDbType.Int).Value = ip_News.Id;
            v_command.Parameters.Add("@Title", SqlDbType.NVarChar).Value = ip_News.Title;
            v_command.Parameters.Add("@ShortContent", SqlDbType.NText).Value = ip_News.ShortContent;
            v_command.Parameters.Add("@Contents", SqlDbType.NText).Value = ip_News.Contents;
            v_command.Parameters.Add("@Image", SqlDbType.VarChar).Value = ip_News.NewsImage;
            v_command.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = ip_News.CreatedDate;
            v_command.Parameters.Add("@AuthorId", SqlDbType.Int).Value = ip_News.AuthorId;
            v_command.Parameters.Add("@Reference", SqlDbType.NVarChar).Value = ip_News.Reference;
            v_command.Parameters.Add("@ViewCount", SqlDbType.Int).Value = ip_News.ViewCount;
            try
            {
                int v_rows = v_command.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
        public bool delete(News ip_News)
        {
            SqlConnection v_conn = BasicDAO<News>.GetConnection();
            if (v_conn.State == ConnectionState.Closed)
            {
                v_conn.Open();
            }
            SqlCommand v_command = new SqlCommand("sp_tblNews_DeleteRow", v_conn);
            v_command.CommandType = CommandType.StoredProcedure;
            v_command.Parameters.Add("@Id", SqlDbType.Int).Value = ip_News.Id;
            try
            {
                int v_rows = v_command.ExecuteNonQuery();
                v_conn.Close();
            }
            catch (Exception exp)
            {
                v_conn.Close();
                HttpContext.Current.Response.Write(exp.ToString());
                return false;
            }
            return true;
        }
        public bool deleteByID(News ip_obj_news)
        {
            return base.deleteByID(ip_obj_news);
        }
        public override List<News> selectAll2List(News ip_obj)
        {
            throw new NotImplementedException();
        }

        public DataTable selectTopNews(int ip_Id)
        {
            DataTable v_dtb = new DataTable();
            v_dtb = exeSpSelectById("[sp_tblNews_SelectTop4]", ip_Id);
            return v_dtb;
        }

        public News selectLatestNews()
        {
            List<News> listnew = convert(exeSpSelect("[sp_tblNews_SelectLatest]"));
            if (listnew == null)
                return null;
            else
            {
                if (listnew.Count > 0)
                    return listnew[0];
                else
                    return null;
            }
        }

        public News selectCompanyNews()
        {
            List<News> listnew = convert(exeSpSelect("[sp_tblNews_selectCompanyNews]"));
            if (listnew == null)
                return null;
            else
            {
                if (listnew.Count > 0)
                    return listnew[0];
                else
                    return null;
            }
        }

        public News selectNewsProduct()
        {
            List<News> listnew = convert(exeSpSelect("[sp_tblNews_selectNewsProduct]"));
            if (listnew == null)
                return null;
            else
            {
                if (listnew.Count > 0)
                    return listnew[0];
                else
                    return null;
            }
        }

        public List<News> SelectInPage(int ip_pageNums, int ip_pageNews)
        {
            DataTable v_dt = new DataTable();

            v_dt = BasicDAO<News>.GetData("[sp_tblNews_SelectInPage]", "@pageNum", ip_pageNums, "@pageNew", ip_pageNews);
            if (v_dt == null || v_dt.Rows.Count == 0)
            {
                return null;
            }
            else
            {
                return convert(v_dt);
            }
        }


        public int SelectNewsCount()
        {
            DataTable v_dt = new DataTable();

            v_dt = BasicDAO<News>.GetData("[sp_tblNews_GetNewCount]");
            if (v_dt == null || v_dt.Rows.Count == 0)
            {
                return 0;
            }
            else
            {
                return int.Parse(v_dt.Rows[0][0].ToString());
            }
        }

    }
}